3D References - use multiple sheets to sumarize on one sheet
https://www.youtube.com/watch?v=EdnAeqxqdzM 21:58
Absolute Cell Reference - use a $ sign in front of the cell referance - =E4*$G$1 - or press F4
Advance Select - make changes to all specific items
https://www.youtube.com/watch?v=PU8ACyYxJBk 21:00
Analysis Tools - add-in in options
https://www.youtube.com/watch?v=PU8ACyYxJBk 17:53
AutoFit Column Width - double click on the space between columns or highlight the entire spreadsheet and double click any one of the spaces
Calendar Picker -
https://www.youtube.com/watch?v=PU8ACyYxJBk 1:41
NETWORKDAYS Function - counts the number of working days between dates
https://www.youtube.com/watch?v=PU8ACyYxJBk 18:53
Concatenate two columns to show in one column - =B3&" "&C3
opposite from Text to Columns - select Data and pick Text to columns -https://www.youtube.com/watch?v=EdnAeqxqdzM 16:59
Split/Combine Names - https://www.youtube.com/watch?v=L9SKTj2gevA 7:21
Conditional Formating -
https://www.youtube.com/watch?v=EdnAeqxqdzM 18:14
CONVERT Function - unit conversions
https://www.youtube.com/watch?v=PU8ACyYxJBk 7:10
Convert Currency Live - x-rates -
https://www.youtube.com/watch?v=PU8ACyYxJBk 8:06
COUNTBLANK() Function -
https://www.youtube.com/watch?v=L9SKTj2gevA 12:26
Data Entry Limits -
https://www.youtube.com/watch?v=L9SKTj2gevA 5:14
Date/Time Shortcut - for the date press Ctrl ; and for time press Ctrl Shift ;
Difference Between Lists
https://www.youtube.com/watch?v=L9SKTj2gevA 4:22
Drop-Down List
Embedding - take a spread sheet table and insert into a word document and update automatically
https://www.youtube.com/watch?v=PU8ACyYxJBk 19:40
Filter - filter data in columns
Advanced Filter - https://www.youtube.com/watch?v=PU8ACyYxJBk 15:12
Return Multiple Results with one function - https://www.youtube.com/watch?v=eqB2mklvWSg
Flash and Auto Fill -
Forcast Sheet - select the data, click on Data tab, click on Forcast Sheet
https://www.youtube.com/watch?v=EdnAeqxqdzM 23:45
Goal Seek - it is part of the what if analysis tool, click on the Data tab, click on What-if-Analysis, select Goal Seek
Hide Cells -
https://www.youtube.com/watch?v=PU8ACyYxJBk 10:21
IF FUNCTION -
https://www.youtube.com/watch?v=EdnAeqxqdzM 12:30
IFERROR Function - =IFERROR(C4/B4,0) - puts a zero if a cell gets a Div by 0 error
https://www.youtube.com/watch?v=EdnAeqxqdzM 27:43
Images in Charts -
https://www.youtube.com/watch?v=EdnAeqxqdzM 11:52
Insert Screenshot - click on Insert tab, pick illustrations, select screenshot
you can crop out what you dont want by using the crop tool on the right
Filled Maps - make sure you have location specific information selected such as (State, City, zipcode, gps coordinates or any other location info)
select Insert and select maps
https://www.youtube.com/watch?v=EdnAeqxqdzM 26:31
Payment Function - PMT() Function -
https://www.youtube.com/watch?v=L9SKTj2gevA 10:49
People Graph -
https://www.youtube.com/watch?v=PU8ACyYxJBk 11:19
PowerPivot - allows to connect to other database type data
https://www.youtube.com/watch?v=EdnAeqxqdzM 19:37
PROPER() Function - capitilize the first letter of each word in cells such as names
https://www.youtube.com/watch?v=L9SKTj2gevA 9:27
Remove Blanks - remove blank rows
https://www.youtube.com/watch?v=PU8ACyYxJBk 10:50
Remove Duplicates - highlight the data, select the Data tab, and click on Remove duplicates
Scenario Manager -
https://www.youtube.com/watch?v=PU8ACyYxJBk 4:21
Show Formulas - shortcut Ctrl ~
https://www.youtube.com/watch?v=EdnAeqxqdzM 15:56
Slicers -
https://www.youtube.com/watch?v=PU8ACyYxJBk 3:08
SUMIFS Function - conditional summing of data
Track Changes -
https://www.youtube.com/watch?v=PU8ACyYxJBk 12:43
Transpose - highligth the data, copy, select a cell somewhere and paste special - select the check box transpose
advance transpose- changing the value in one will change the value in the other- https://www.youtube.com/watch?v=PU8ACyYxJBk 0:49
Trend Line in Charts -
https://www.youtube.com/watch?v=L9SKTj2gevA 6:19
VLOOKUP - finds data in a vertical list - if you change the data in the column list it will automatically up date in the VLOOKUP cell
HLOOKUP - finds data in a horizontal list
https://www.youtube.com/watch?v=EdnAeqxqdzM 6:55
What if Analysis Tool - Goal Seek
XLOOKUP -
https://www.youtube.com/watch?v=xxX8XiuKFEY
